﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports BusinessRule.DTO
Imports System.Configuration
Imports DatabaseAccess.DBUtility

Public Class uMESContainerInfoPrintBusiness
    Implements uMESContainerInfoPrintIBusiness

    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

    ''' <summary>
    ''' 获取批次信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerCurrentInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerCurrentInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainername, strFactoryName, strProductName, strEmployeeName, strWorkcenterName As String

            strContainername = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainername = QueryCondition("ContainerName").ToString()
            End If

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            strWorkcenterName = "" '增加工区条件过滤 wzj 2017.11.11
            If QueryCondition.Keys.Contains("WorkcenterName") = True Then
                strWorkcenterName = QueryCondition("WorkcenterName").ToString()
            End If

            strProductName = ""
            If QueryCondition.Keys.Contains("ProductName") = True Then
                strProductName = QueryCondition("ProductName").ToString()
            End If

            strEmployeeName = ""
            If QueryCondition.Keys.Contains("EmployeeName") = True Then
                strEmployeeName = QueryCondition("EmployeeName").ToString()
            End If
            'strSQL = <string>
            '               SELECT    c.Containername, c.Qty,pb.Productname,  p.Description,    p.Productrevision, c.Factorystartdate, 
            '            NVL(p.bomid,'') AS bomid, NVL(p.bombaseid,'') as bombaseid ,c.Containerid, e.Fullname,  
            '            cdo.Cdoname,decode(mt.materialtypename,'管材',0,c.isselfmfg) isselfmfg,wc.WORKCENTERNAME,fa.FactoryName,m.Mfgordername,P.PRODUCTID,DECODE(CDO.CDONAME,'Workflow',c.mfgworkflowid,w.workflowid) workflowid FROM Container c 
            '            LEFT JOIN Workcenter wc ON wc.workcenterid=c.workcenterid  
            '            INNER JOIN Product p on p.Productid = c.Productid   
            '            INNER JOIN Productbase pb on pb.Productbaseid =p.Productbaseid   
            '            INNER JOIN Currentstatus cu ON cu.Currentstatusid = c.Currentstatusid   
            '            INNER JOIN WorkflowStep ws ON cu.WorkflowStepid = ws.WorkflowStepid   
            '            INNER JOIN Workflow w ON w.Workflowid = ws.Workflowid   
            '            INNER JOIN Cdodefinition cdo ON cdo.Cdodefid = w.Cdotypeid   
            '            LEFT OUTER JOIN Factory fa ON cu.Factoryid = fa.Factoryid  
            '            INNER JOIN Employee e ON e.Employeeid = c.startemployeeid  
            '            left  JOIN Mfgorder m ON m.mfgorderid=c.mfgorderid  
            '            LEFT JOIN materialtype mt ON mt.materialtypeid=p.materialtypeid 
            '            WHERE c.Parentcontainerid IS NULL AND c.Status = 1 AND c.Qty >= 0 AND c.Containerid != c.Containername  
            '            AND ws.WorkflowStepName!='工艺确认'
            '          </string>

            '增加 p.istopgz add zhunan 2022.3.14
            strSQL = <string>
                         SELECT    c.Containername,c.ypcontainertype AS yptype, c.Qty,cci.Productname,cci.productdescription Description, cci.productrevision, c.Factorystartdate, 
                        NVL(p.bomid,'') AS bomid, NVL(p.bombaseid,'') as bombaseid ,c.Containerid, e.Fullname,c.containertype,  
                        cdo.Cdoname,decode(mt.materialtypename,'管材',0,c.isselfmfg) isselfmfg, cci.fromworkcentername WORKCENTERNAME,cci.FactoryName,                      
                        cci.mfgordername,P.PRODUCTID,DECODE(CDO.CDONAME,'Workflow',c.mfgworkflowid,w.workflowid) workflowid,swc.workflowid faworkflowid,
						(NVL(cci.workflowroute,cci.productname)||'/'||REPLACE(cci.containername,cci.productname||'/','')) containerBatch,DECODE(pt.producttypename,'子工艺虚拟件'，'子卡','主卡') MainOrSonContainer,
						p.firstusedproduct, c.containercomments,p.istopgz,p.workflowroute
                        FROM Container c                         
                        LEFT JOIN containercurrentinfo cci ON cci.containerid=c.containerid                         
                        INNER JOIN Product p on p.Productid = c.Productid  
                        LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid                                             
                        INNER JOIN Workflow w ON w.Workflowid = NVL(cci.bsworkflowid,cci.Workflowid)                     
                        INNER JOIN Cdodefinition cdo ON cdo.Cdodefid = w.Cdotypeid                     
                        LEFT  JOIN Factory fa ON  fa.Factoryid  =cci.originalfactoryid                   
                        INNER JOIN Employee e ON e.Employeeid = c.startemployeeid                      
                        LEFT JOIN materialtype mt ON mt.materialtypeid=p.materialtypeid                 
						LEFT JOIN (
								SELECT swc.containerid,swc.subworkflowid,max(swc.createdate) MAXcreatedate FROM subworkflowcheckinfo swc
									GROUP BY swc.containerid,swc.subworkflowid
								--ORDER BY swc.createdate DESC
						) a ON a.containerid=cci.containerid AND a.subworkflowid=cci.workflowid 
						LEFT JOIN SUBWORKFLOWCHECKINFO SWC ON SWC.CONTAINERID=CCI.CONTAINERID AND SWC.SUBWORKFLOWID=CCI.WORKFLOWID AND a.MAXcreatedate=swc.createdate
                        WHERE c.Parentcontainerid IS NULL AND c.Status = 1 AND c.Qty >= 0 AND c.Containerid != c.Containername AND CCI.WorkflowStepName!='工艺确认'
                     </string>
            If strContainername <> "" Then
                strSQL = strSQL & "AND c.Containername LIKE '%" & strContainername & "%'" & vbCrLf
            End If

            'AND  Cdodefinition.Cdoname = 'MachineMfgWorkFlow' 
           
            If strProductName <> "" Then
                strSQL = strSQL & "AND cci.Productname like '%" & strProductName & "%'" & vbCrLf
            End If
            If strEmployeeName <> "" Then
                strSQL = strSQL & "AND UPPER(e.Fullname) LIKE UPPER('%" & strEmployeeName & "%')" & vbCrLf
            End If
            If QueryCondition.Keys.Contains("IsselfMfg") Then
                strSQL += String.Format(" AND c.isselfmfg={0}", QueryCondition("IsselfMfg"))
            End If
            If strWorkcenterName <> "" Then '增加工区条件过滤 wzj 2017.11.11
                strSQL = strSQL & " And cci.fromworkcentername ='" & strWorkcenterName & "'" & vbCrLf
            End If

            If QueryCondition.ContainsKey("ProductID") Then
                strSQL = strSQL & " And p.ProductID ='" & QueryCondition("ProductID") & "'" & vbCrLf
            End If

            If QueryCondition.ContainsKey("WorkFlowID") Then '此时选择的是对应的工艺
                strSQL += String.Format(" and (cci.WorkFlowID='{0}' OR SWC.WORKFLOWID='{0}' OR C.MFGWORKFLOWID='{0}')", QueryCondition("WorkFlowID")) & vbCrLf
            Else
                If strFactoryName <> "" Then
                    strSQL = strSQL & "AND (fa.Factoryname='" & strFactoryName & "' or cci.Factoryname='" + strFactoryName + "')" & vbCrLf
                End If
            End If

            If QueryCondition.ContainsKey("MfgOrderNames") Then
                strSQL += String.Format(" and cci.mfgordername in ({0})", QueryCondition("MfgOrderNames"))
            End If

            If QueryCondition.ContainsKey("StartDate") Then
                strSQL += String.Format(" AND c.Originalstartdate>=to_Date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", QueryCondition("StartDate"))
            End If

            If QueryCondition.ContainsKey("EndDate") Then
                strSQL += String.Format(" AND c.Originalstartdate<=to_Date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", QueryCondition("EndDate"))
            End If
            'add by longb 流水卡 工序检测项调整的记录筛选
            If QueryCondition.ContainsKey("type") Then
                If QueryCondition("type").Equals("2") Then
                    Dim str As String = <string>and Exists ( select 1 from stepcheckpointresult scr 
                        inner join checkpointresultupdaterecord c on c.stepcheckpointresultid =scr.stepcheckpointresultid 
                        where scr.containerid=c.containerid)</string>
                    strSQL += str
                End If
            End If

            '增加 p.istopgz add zhunan 2022.3.14
            strSQL += <string>
                        group by c.Containername,c.ypcontainertype, c.Qty,cci.Productname,cci.productdescription,cci.Productrevision, 
                        c.Factorystartdate,p.bomid,p.bombaseid,c.Containerid, e.Fullname,c.containertype,cdo.Cdoname,mt.materialtypename,c.isselfmfg,
                        cci.fromworkcentername,cci.FactoryName,cci.Mfgordername,P.PRODUCTID,c.mfgworkflowid,w.workflowid,swc.workflowid,
                        cci.workflowroute,cci.containername,pt.producttypename,p.firstusedproduct, c.containercomments,p.istopgz,p.workflowroute
                        ORDER BY cci.Mfgordername,c.CONTAINERNAME
                      </string>.Value

            'Dim dt As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL
            Dim result As New uMESProcessResult
            oResult.Result = True
            oResult = dal.RunProcedureForPage(paraPage)

            'oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", oResult.DBTable)

            For Each row As DataRow In oResult.DBTable.Rows
                If row.IsNull("bombaseid") = False Then
                    If row("bombaseid").ToString().Trim() = "0000000000000000" Then
                        row("bombaseid") = ""
                    End If
                Else
                    row("bombaseid") = ""
                End If

                If row.IsNull("bomid") = False Then
                    If row("bomid").ToString().Trim() = "0000000000000000" Then
                        row("bomid") = ""
                    End If
                Else
                    row("bomid") = ""
                End If
            Next

            Return oResult

        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 获取领料批次信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerCurrentInfoERP(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerCurrentInfoERP
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainername, strFactoryName, strProductName, strEmployeeName As String

            strContainername = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainername = QueryCondition("ContainerName").ToString()
            End If

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            strProductName = ""
            If QueryCondition.Keys.Contains("ProductName") = True Then
                strProductName = QueryCondition("ProductName").ToString()
            End If

            strEmployeeName = ""
            If QueryCondition.Keys.Contains("EmployeeName") = True Then
                strEmployeeName = QueryCondition("EmployeeName").ToString()
            End If

            strSQL = "SELECT " & vbCrLf & _
                    "       Container.Containername, Container.Qty,Productbase.Productname," & vbCrLf & _
                    "       Product.Description, " & vbCrLf & _
                    "       Product.Productrevision, Container.Factorystartdate, NVL(Product.bomid,'') AS bomid, NVL(Product.bombaseid,'') as bombaseid ,Container.Containerid, Employee.Fullname," & vbCrLf & _
                    "Cdodefinition.Cdoname,ut.attributevalue, ut1.attributevalue as BeginStep, ut2.attributevalue as CloseContianer FROM Container" & vbCrLf & _
                    "INNER JOIN Product on Product.Productid = Container.Productid " & vbCrLf & _
                    "INNER JOIN Productbase on Productbase.Productbaseid =Product.Productbaseid " & vbCrLf & _
                    "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid " & vbCrLf & _
                    "INNER JOIN WorkflowStep ON Currentstatus.WorkflowStepid = WorkflowStep.WorkflowStepid " & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = WorkflowStep.Workflowid " & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid " & vbCrLf & _
                    "LEFT OUTER JOIN Factory ON Currentstatus.Factoryid = Factory.Factoryid" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = Container.startemployeeid" & vbCrLf & _
                    "LEFT JOIN userattribute ut ON ut.parentid=container.containerid AND ut.userattributename='printType'" & vbCrLf & _
                    "LEFT JOIN userattribute ut1 ON ut1.parentid=container.containerid AND ut1.userattributename='BeginStep'" & vbCrLf & _
                    "LEFT JOIN userattribute ut2 ON ut2.parentid=container.containerid AND ut2.userattributename='CloseContianer'" & vbCrLf & _
   "LEFT JOIN userattribute ut3 ON ut3.parentid=container.containerid AND ut3.userattributename in " & vbCrLf & _
   " ('材料牌号','实发数/顺序号','熔炉号','检验编号','库房保管','器材检验','代料牌号','代料规格','代料单号','平行作业单号','平行作业工序')" & vbCrLf & _
 " left join movehistory m on m.historyid = Container.containerid and m.pathid is not null" & vbCrLf & _
            " left join workflowstep wfs on wfs.workflowstepid=m.stepid" & vbCrLf & _
             " left join workflow wf on wf.workflowid = wfs.workflowid" & vbCrLf & _
             " left join workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid" & vbCrLf & _
               "WHERE Container.Parentcontainerid IS NULL AND Container.Status = 1 AND Container.Qty >= 0 AND Container.Containerid <> Container.Containername" & vbCrLf & _
                " and ut3.attributevalue is null and ut.attributevalue is not null and wfb.workflowname='MW' and wf.workflowrevision='1' and wfs.sequence=1" & vbCrLf

            If strContainername <> "" Then
                strSQL = strSQL & "AND Container.Containername LIKE '%" & strContainername & "%'" & vbCrLf
            End If

            'AND  Cdodefinition.Cdoname = 'MachineMfgWorkFlow' 
            If strFactoryName <> "" Then
                strSQL = strSQL & "AND Factory.Factoryname='" & strFactoryName & "'" & vbCrLf
            End If
            If strProductName <> "" Then
                strSQL = strSQL & "AND Productbase.Productname='" & strProductName & "'" & vbCrLf
            End If
            If strEmployeeName <> "" Then
                strSQL = strSQL & "AND UPPER(Employee.Fullname) LIKE UPPER('%" & strEmployeeName & "%')" & vbCrLf
            End If
            strSQL = strSQL & "ORDER BY container.Containername " & vbCrLf ''Container.Factorystartdate, Container.Containername" & vbCrLf

            'Dim dt As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
            paraPage.strSQL = strSQL
            Dim result As New uMESProcessResult
            oResult.Result = True
            oResult = dal.RunProcedureForPage(paraPage)

            oResult.DBTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", oResult.DBTable)

            For Each row As DataRow In oResult.DBTable.Rows
                If row.IsNull("bombaseid") = False Then
                    If row("bombaseid").ToString().Trim() = "0000000000000000" Then
                        row("bombaseid") = ""
                    End If
                Else
                    row("bombaseid") = ""
                End If

                If row.IsNull("bomid") = False Then
                    If row("bomid").ToString().Trim() = "0000000000000000" Then
                        row("bomid") = ""
                    End If
                Else
                    row("bomid") = ""
                End If
            Next

            Return oResult

        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 获取跟踪卡打印信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerPrintInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerPrintInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If

            strSQL = "SELECT Container.Containername, Factory.Factoryname, Inventtaskinfo.Inventtaskinfoname, Productfamily.Productfamilyname," & vbCrLf & _
                    "Productbase.Productname, Product.Description AS ProductDesc, Container.Originalqty, UOM.Uomname, '' AS ProductNo," & vbCrLf & _
                    "Inventtaskinfo.InventSizeID, Product.Workflowroute, '' AS GuidelinesDoc, Container.Materialrequirementdate," & vbCrLf & _
                    "PlanEmployee.Fullname AS PlanFullName, Product.Materialtrademark AS PlanMaterialtrademark,  Product.MaterialSpecification AS PlanMaterialSpecification," & vbCrLf & _
                    "Product.Materialstatus AS PlanMaterialstatus, Product.Materialsize AS PlanMaterialsize, '' AS PlanFurnaceNumber, " & vbCrLf & _
                    "Product.Technicalconditions AS PlanTechnicalconditions, Materialtype.Materialtypename, Phasemark.Phasemarkname, Container.Containercomments, Container.Qualificationnum" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "LEFT OUTER JOIN Mfgorder ON Container.mfgorderid=Mfgorder.mfgorderid " & vbCrLf & _
                    "LEFT OUTER JOIN Inventtaskinfo ON Mfgorder.Inventtaskinfoid = Inventtaskinfo.Inventtaskinfoid" & vbCrLf & _
                    "INNER JOIN Product on Product.Productid = Container.Productid " & vbCrLf & _
                    "INNER JOIN Productbase on Productbase.Productbaseid =Product.Productbaseid " & vbCrLf & _
                    "LEFT OUTER JOIN Productfamily on Productfamily.Productfamilyid=Product.Productfamilyid " & vbCrLf & _
                    "LEFT OUTER JOIN Materialtype ON Materialtype.Materialtypeid = Product.Materialtypeid" & vbCrLf & _
                    "LEFT OUTER JOIN Factory ON Product.Factoryid = Factory.Factoryid" & vbCrLf & _
                    "INNER JOIN Historymainline ON Historymainline.Containerid = Container.Containerid" & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Historymainline.Txntype" & vbCrLf & _
                    "INNER JOIN Employee PlanEmployee ON PlanEmployee.Employeeid = Historymainline.Employeeid" & vbCrLf & _
                    "LEFT OUTER JOIN Phasemark ON Product.Phasemarkid = Phasemark.Phasemarkid" & vbCrLf & _
                    "LEFT OUTER JOIN UOM ON Container.Uomid = UOM.Uomid" & vbCrLf & _
                    "WHERE Container.ContainerID = '" & strContainerid & "' AND Cdodefinition.Cdoname = 'Start'" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            strSQL = "SELECT Datapointhistorydetail.Dataname, Datapointhistorydetail.Datavalue, Employee.FullName, Employee.Paycard" & vbCrLf & _
                    "FROM Datapointhistory " & vbCrLf & _
                    "INNER JOIN Historymainline ON Datapointhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(SELECT Historymainline.Containerid, Historymainline.Workflowstepid, MAX(Historymainline.Txndate) AS Txndate" & vbCrLf & _
                    "FROM Datapointhistory " & vbCrLf & _
                    "INNER JOIN Historymainline ON Datapointhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN Workflowstep ON Historymainline.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(" & vbCrLf & _
                    "SELECT Container.Containerid, (CASE WHEN Cdodefinition.Cdoname = 'MachineMfgWorkFlow' THEN 9999 ELSE Workflowstep.Sequence END) AS SpecIndex" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Currentstatus ON Container.Currentstatusid= Currentstatus.Currentstatusid" & vbCrLf & _
                    "INNER JOIN Workflowstep ON Currentstatus.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = Workflowstep.Workflowid" & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Workflow.Cdotypeid = Cdodefinition.Cdodefid" & vbCrLf & _
                    ") CSpec" & vbCrLf & _
                    "ON CSpec.Containerid= Historymainline.ContainerID AND CSpec.SpecIndex >= Workflowstep.Sequence" & vbCrLf & _
                    "WHERE Historymainline.reversalstatus = 1 " & vbCrLf & _
                    "GROUP BY Historymainline.Containerid, Historymainline.Workflowstepid" & vbCrLf & _
                    ") CMax" & vbCrLf & _
                    "ON CMax.Containerid = Historymainline.Containerid" & vbCrLf & _
                    "AND CMax.Workflowstepid = Historymainline.Workflowstepid" & vbCrLf & _
                    "AND CMax.Txndate = Historymainline.Txndate" & vbCrLf & _
                    "INNER JOIN Datapointhistorydetail ON Datapointhistorydetail.Datapointhistoryid = Datapointhistory.Datapointhistoryid" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = Historymainline.Employeeid" & vbCrLf & _
                    "WHERE Historymainline.reversalstatus = 1 AND Historymainline.ContainerID = '" & strContainerid & "'" & vbCrLf & _
                    "ORDER BY Historymainline.Txndate" & vbCrLf

            Dim dtDataCol As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            strSQL = "SELECT Container.Containername" & vbCrLf & _
                    "FROM Container " & vbCrLf & _
                    "WHERE Container.Parentcontainerid = '" & strContainerid & "'" & vbCrLf

            Dim dtChild As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            strSQL = "SELECT DISTINCT Employee.Employeename, Specbase.Specname, Employee.FullName, Employee.Paycard, TO_CHAR(Historymainline.Txndate,'RRRR-MM-DD') AS Txndate,cdo1.cdoname" & vbCrLf & _
                    "FROM Movehistory " & vbCrLf & _
                    "INNER JOIN Historymainline ON Movehistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(SELECT Historymainline.Containerid, Historymainline.Workflowstepid, MAX(Historymainline.Txndate) AS Txndate" & vbCrLf & _
                    "FROM Movehistory " & vbCrLf & _
                    "INNER JOIN Historymainline ON Movehistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN Workflowstep ON Historymainline.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(" & vbCrLf & _
                    "SELECT Container.Containerid, (CASE WHEN Cdodefinition.Cdoname = 'MachineMfgWorkFlow' THEN 9999 ELSE Workflowstep.Sequence END) AS SpecIndex" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Currentstatus ON Container.Currentstatusid= Currentstatus.Currentstatusid" & vbCrLf & _
                    "INNER JOIN Workflowstep ON Currentstatus.Workflowstepid = Workflowstep.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = Workflowstep.Workflowid" & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Workflow.Cdotypeid = Cdodefinition.Cdodefid" & vbCrLf & _
                    ") CSpec" & vbCrLf & _
                    "ON CSpec.Containerid= Historymainline.ContainerID AND CSpec.SpecIndex >= Workflowstep.Sequence" & vbCrLf & _
                    "WHERE Historymainline.reversalstatus = 1 " & vbCrLf & _
                    "GROUP BY Historymainline.Containerid, Historymainline.Workflowstepid" & vbCrLf & _
                    ") CMax" & vbCrLf & _
                    "ON CMax.Containerid = Historymainline.Containerid" & vbCrLf & _
                    "AND CMax.Workflowstepid = Historymainline.Workflowstepid" & vbCrLf & _
                    "AND CMax.Txndate = Historymainline.Txndate" & vbCrLf & _
                    "INNER JOIN Employee ON Historymainline.Employeeid = Employee.Employeeid" & vbCrLf & _
                    "INNER JOIN " & vbCrLf & _
                    "(" & vbCrLf & _
                    "SELECT Workflowstep.Workflowid, Workflowstep.Workflowstepid, " & vbCrLf & _
                    "       (CASE WHEN Workflowstep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Spec.Specid END) AS Specid" & vbCrLf & _
                    "FROM Workflowstep " & vbCrLf & _
                    "LEFT OUTER JOIN Spec ON Workflowstep.Specid = Spec.Specid" & vbCrLf & _
                    "LEFT OUTER JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid" & vbCrLf & _
                    ") CStep" & vbCrLf & _
                    "ON Movehistory.Stepid = CStep.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = CStep.Workflowid" & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid" & vbCrLf & _
                    "INNER JOIN Spec ON CStep.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                    "INNER JOIN Container ON Container.ContainerID = Historymainline.ContainerID" & vbCrLf & _
                     "left JOIN cdodefinition cdo1 ON Historymainline.txntype = cdo1.cdodefid " & vbCrLf & _
                    "WHERE Historymainline.reversalstatus = 1 AND Cdodefinition.Cdoname = 'Workflow'" & vbCrLf & _
                    "AND Container.ContainerID = '" & strContainerid & "'" & vbCrLf

            Dim dtMove As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            Dim dtMaterialinfo As New DataTable
            Dim strDataName, strDataNameForNotMetal, strMaterialinfoName, strQualificationnum As String
            strDataName = ConfigurationSettings.AppSettings("MaterialinfoName")
            strDataNameForNotMetal = ConfigurationSettings.AppSettings("MaterialinfoNameForNotMetal")
            strQualificationnum = ""
            If dtMain.Rows.Count >= 0 Then
                If dtMain.Rows(0).IsNull("Qualificationnum") = False Then
                    strQualificationnum = dtMain.Rows(0).Item("Qualificationnum")
                End If
            End If

            If strQualificationnum <> "" Then
                strSQL = "SELECT Materialinfo.Materialinfoname,  Materialinfo.Materialtrademark,  Materialinfo.MaterialSpecification," & vbCrLf & _
                                   "       Materialinfo.Materialsize, Materialinfo.FurnaceNumber, Materialinfo.MaterialMfgDepartment, Materialinfo.MaterialMfgDate," & vbCrLf & _
                                   "       Materialinfo.MaterialContainer, Materialinfo.QualificationNum, Materialinfo.StoredDate, Materialinfo.ItemId," & vbCrLf & _
                                   "       Materialinfo.PerItemName, Materialinfo.Description, Materialinfo.Hotprocessstatus, Materialinfo.Inventnos, Materialinfo.RepeatCheckDate,Materialinfo.Notes,Materialinfo.Recid,container.qualificationqty" & vbCrLf & _
                                   "FROM Materialinfo" & vbCrLf & _
                                   "left join container on container.qualificationnum=Materialinfo.Qualificationnum" & vbCrLf & _
                                   "left join  product on product.productid=container.productid" & vbCrLf & _
                                  "WHERE Materialinfo.MaterialinfoName = '" & strQualificationnum & "' " & vbCrLf
                dtMaterialinfo = dal.GetQualityRecordInfoForProductNo(strSQL)
            Else
                If dtDataCol.Rows.Count > 0 Then
                    Dim drs As DataRow() = dtDataCol.Select("Dataname = '" & strDataName & "' OR Dataname = '" & strDataNameForNotMetal & "'")
                    If drs.Length > 0 Then
                        If drs(0).IsNull("Datavalue") = False Then
                            strMaterialinfoName = drs(0).Item("Datavalue")
                            If strMaterialinfoName <> "" Then
                                strSQL = "SELECT Materialinfo.Materialinfoname,  Materialinfo.Materialtrademark,  Materialinfo.MaterialSpecification," & vbCrLf & _
                                        "       Materialinfo.Materialsize, Materialinfo.FurnaceNumber, Materialinfo.MaterialMfgDepartment, Materialinfo.MaterialMfgDate," & vbCrLf & _
                                        "       Materialinfo.MaterialContainer, Materialinfo.QualificationNum, Materialinfo.StoredDate, Materialinfo.ItemId," & vbCrLf & _
                                        "       Materialinfo.PerItemName, Materialinfo.Description, Materialinfo.Hotprocessstatus, Materialinfo.Inventnos, Materialinfo.RepeatCheckDate,Materialinfo.Notes,Materialinfo.Recid,container.qualificationqty" & vbCrLf & _
                                        "FROM Materialinfo" & vbCrLf & _
                                        "left join container on container.qualificationnum=Materialinfo.Qualificationnum" & vbCrLf & _
                                        "left join  product on product.productid=container.productid" & vbCrLf & _
                                      "WHERE Materialinfo.MaterialinfoName = '" & strMaterialinfoName & "'" & vbCrLf
                                dtMaterialinfo = dal.GetQualityRecordInfoForProductNo(strSQL)
                            End If
                        End If
                    End If
                End If
            End If

            '可供规格和牌号字段'
            strSQL = "select  dd.paihao,gg.guige " & vbCrLf & _
                    "from container" & vbCrLf & _
                   " Left Join" & vbCrLf & _
                   "(" & vbCrLf & _
                  "select d.datavalue as paihao,hmll.containerid" & vbCrLf & _
                   "from datapointhistorydetail d" & vbCrLf & _
                   "left join datapointhistory dh on dh.datapointhistoryid = d.datapointhistoryid " & vbCrLf & _
                   "left join historymainline hmll on hmll.historymainlineid=dh.historymainlineid " & vbCrLf & _
                   "where d.dataname='可供牌号'" & vbCrLf & _
                    "order by hmll.txndate desc " & vbCrLf & _
                   ")dd on dd.containerid=container.containerid and rownum<2" & vbCrLf & _
                  "Left  Join" & vbCrLf & _
                   "(" & vbCrLf & _
                   "select d.datavalue as guige,hmll.containerid " & vbCrLf & _
                    "from datapointhistorydetail d" & vbCrLf & _
                    "left join datapointhistory dh on dh.datapointhistoryid = d.datapointhistoryid  " & vbCrLf & _
                    "left join historymainline hmll on hmll.historymainlineid=dh.historymainlineid  " & vbCrLf & _
                    "where d.dataname='可供规格'" & vbCrLf & _
                     "order by hmll.txndate desc " & vbCrLf & _
                     ")gg on gg.containerid=container.containerid and rownum<2" & vbCrLf & _
                     "where container.containerid='" & strContainerid & "'"
            Dim dtPaihaoGuige As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)


            strSQL = "SELECT Spec.Specno, Factory.Factoryname, Specbase.Specname, Spec.Opdesc, CInfo.Sequence, " & vbCrLf & _
                "       CInfo.Qualifiedqty, CInfo.Defectqty, CInfo.Floorqty, " & vbCrLf & _
                "       CInfo.Scrapqty,  CInfo.Fullname, CInfo.Checktype, TO_Char(CInfo.Txndate,'RRRR-MM-DD') AS Txndate, CInfo.Paycard, (CASE WHEN (NVL(CInfo.UserFactoryName,'') = NVL(CInfo.ConFactoryName,'')) THEN 1 ELSE NULL END) AS NeedPrint,syner.Synergictype" & vbCrLf & _
                "FROM " & vbCrLf & _
                "( " & vbCrLf & _
                "SELECT Container.ContainerID, (CASE WHEN AllStep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Spec.Specid END) AS Specid, AllStep.Sequence,  " & vbCrLf & _
                "       NULL AS Qualifiedqty, NULL AS Defectqty, NULL AS Floorqty, " & vbCrLf & _
                "       NULL AS Scrapqty,  NULL AS Fullname, NULL AS Checktype, NULL AS Txndate, NULL AS Paycard, NULL AS UserFactoryName, CFactory.FactoryName AS ConFactoryName" & vbCrLf & _
                "FROM Container " & vbCrLf & _
                "INNER JOIN Currentstatus ON Container.Currentstatusid = Currentstatus.Currentstatusid " & vbCrLf & _
                "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Currentstatus.Workflowstepid " & vbCrLf & _
                "INNER JOIN Workflow ON Workflow.Workflowid = WorkflowStep.Workflowid  " & vbCrLf & _
                "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid " & vbCrLf & _
                "INNER JOIN Workflowstep AllStep ON AllStep.Workflowid = Container.Mfgworkflowid " & vbCrLf & _
                "LEFT OUTER JOIN Spec ON AllStep.Specid = Spec.Specid " & vbCrLf & _
                "LEFT OUTER JOIN Specbase ON Specbase.Specbaseid = AllStep.Specbaseid " & vbCrLf & _
                "LEFT OUTER JOIN Factory CFactory ON Container.Originalfactoryid = CFactory.Factoryid" & vbCrLf & _
                "WHERE Cdodefinition.Cdoname <> 'MachineMfgWorkFlow' AND Container.Parentcontainerid IS NULL " & vbCrLf & _
                "UNION  " & vbCrLf & _
                "SELECT Container.ContainerID, (CASE WHEN AllStep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Spec.Specid END) AS Specid, AllStep.Sequence,  " & vbCrLf & _
                "       NULL AS Qualifiedqty, NULL AS Defectqty, NULL AS Floorqty, " & vbCrLf & _
                "       NULL AS Scrapqty,  NULL AS Fullname, NULL AS Checktype, NULL AS Txndate, NULL AS Paycard, NULL AS UserFactoryName, CFactory.FactoryName AS ConFactoryName " & vbCrLf & _
                "FROM Container " & vbCrLf & _
                "INNER JOIN Currentstatus ON Container.Currentstatusid = Currentstatus.Currentstatusid " & vbCrLf & _
                "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Currentstatus.Workflowstepid " & vbCrLf & _
                "INNER JOIN Workflow ON Workflow.Workflowid = WorkflowStep.Workflowid  " & vbCrLf & _
                "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid " & vbCrLf & _
                "INNER JOIN Workflowstep AllStep ON AllStep.Workflowid = Workflowstep.Workflowid " & vbCrLf & _
                "AND AllStep.Sequence >= Workflowstep.Sequence " & vbCrLf & _
                "LEFT OUTER JOIN Factory CFactory ON Container.Originalfactoryid = CFactory.Factoryid" & vbCrLf & _
                "LEFT OUTER JOIN Spec ON AllStep.Specid = Spec.Specid " & vbCrLf & _
                "LEFT OUTER JOIN Specbase ON Specbase.Specbaseid = AllStep.Specbaseid " & vbCrLf & _
                "LEFT OUTER JOIN  " & vbCrLf & _
                "(SELECT Historymainline.Containerid,  Historymainline.Workflowstepid, Speclastcheckhistory.Speclastcheckhistoryid " & vbCrLf & _
                "FROM Historymainline  " & vbCrLf & _
                "INNER JOIN Speclastcheckhistory ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid " & vbCrLf & _
                ") CLastCheck " & vbCrLf & _
                "ON CLastCheck.Containerid = Container.Containerid " & vbCrLf & _
                "AND CLastCheck.Workflowstepid = Currentstatus.Workflowstepid " & vbCrLf & _
                "WHERE Cdodefinition.Cdoname = 'MachineMfgWorkFlow' AND Container.Parentcontainerid IS NULL AND CLastCheck.Speclastcheckhistoryid IS NULL " & vbCrLf & _
                "UNION  " & vbCrLf & _
                "SELECT Container.ContainerID, (CASE WHEN Workflowstep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Spec.Specid END) AS Specid, Workflowstep.Sequence, " & vbCrLf & _
                "       Speclastcheckhistory.Qualifiedqty, Speclastcheckhistory.Defectqty, Speclastcheckhistory.Floorqty, " & vbCrLf & _
                "       Speclastcheckhistory.Scrapqty, Employee.Fullname, Speclastcheckhistory.Checktype, Historymainline.Txndate, Employee.Paycard, Factory.FactoryName AS UserFactoryName, CFactory.FactoryName AS ConFactoryName" & vbCrLf & _
                "FROM Speclastcheckhistory " & vbCrLf & _
                "INNER JOIN Historymainline ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid " & vbCrLf & _
                "INNER JOIN Container ON Historymainline.Containerid = Container.Containerid  " & vbCrLf & _
                "INNER JOIN Workflowstep ON Historymainline.Workflowstepid = Workflowstep.Workflowstepid " & vbCrLf & _
                "LEFT OUTER JOIN Spec ON Workflowstep.Specid = Spec.Specid " & vbCrLf & _
                "LEFT OUTER JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid " & vbCrLf & _
                "INNER JOIN Employee ON Historymainline.Employeeid = Employee.Employeeid " & vbCrLf & _
                "LEFT JOIN Sessionvalues ON Employee.Employeeid = Sessionvalues.Employeeid" & vbCrLf & _
                "LEFT JOIN Factory ON Sessionvalues.Factoryid = Factory.Factoryid" & vbCrLf & _
                "LEFT OUTER JOIN Factory CFactory ON Container.Originalfactoryid = CFactory.Factoryid" & vbCrLf & _
                ") CInfo " & vbCrLf & _
                "INNER JOIN Spec ON CInfo.Specid = Spec.Specid " & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & vbCrLf & _
                "LEFT OUTER JOIN Factory ON Spec.Factoryid = Factory.Factoryid " & vbCrLf & _
                "left join " & vbCrLf & _
                "(" & vbCrLf & _
                "select  synergicinfo.containerid,spec.specid,specbase.specname,SynergicSpecInfo.Synergictype" & vbCrLf & _
                "from synergicinfo " & vbCrLf & _
                "INNER JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid" & vbCrLf & _
                "INNER JOIN SynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicspecinfoid = SynergicSpecInfo.Synergicspecinfoid" & vbCrLf & _
                "INNER JOIN Spec ON SynergicSpecInfo.Specid = Spec.Specid" & vbCrLf & _
                "inner join specbase on specbase.specbaseid=spec.specbaseid" & vbCrLf & _
                ")syner on syner.containerid=CInfo.ContainerID and syner.specid=CInfo.Specid" & vbCrLf & _
                "WHERE CInfo.ContainerID = '" & strContainerid & "' " & vbCrLf & _
                "ORDER BY CInfo.ContainerID, CInfo.Sequence"

            Dim dtSpecLastCheck As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            dtSpecLastCheck = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtSpecLastCheck)

            strSQL = "SELECT DISTINCT WorkCenterDispatching.Containerid, Specbase.Specname, Employee.FullName, MfgEmployee.Fullname AS MfgEmployeeName," & vbCrLf & _
                "       MutualcheckEmployee.Fullname AS MutualcheckEmployeeName, CheckEmployee.Paycard, Historymainline.Txndate," & vbCrLf & _
                "       (CASE WHEN NVL(Conventioncheckhistory.Eligibilityqty, 0) > 0 THEN CheckEmployee.Fullname ELSE '' END) AS CheckEmployeeName" & vbCrLf & _
                "FROM WorkCenterDispatching  " & vbCrLf & _
                "INNER JOIN Workingreporthistory ON WorkCenterDispatching.WorkCenterDispatchingid = Workingreporthistory.WorkCenterDispatchingid " & vbCrLf & _
                "LEFT OUTER JOIN Conventioncheckhistory ON Workingreporthistory.Workingreporthistoryid = Conventioncheckhistory.Workingreportid " & vbCrLf & _
                "INNER JOIN Historymainline ON Historymainline.Historymainlineid = Workingreporthistory.Historymainlineid " & vbCrLf & _
                "LEFT OUTER JOIN Historymainline Historymainline2 ON Historymainline2.Historymainlineid = Conventioncheckhistory.Historymainlineid " & vbCrLf & _
                "INNER JOIN Spec ON WorkCenterDispatching.Specid = Spec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "INNER JOIN Employee ON Employee.Employeeid = WorkCenterDispatching.Dispatchemployeeid" & vbCrLf & _
                "INNER JOIN Employee MfgEmployee ON MfgEmployee.Employeeid = Historymainline.Employeeid" & vbCrLf & _
                "LEFT OUTER JOIN Employee MutualcheckEmployee ON MutualcheckEmployee.Employeeid = Workingreporthistory.Mutualcheckemployeeid" & vbCrLf & _
                "LEFT OUTER JOIN Employee CheckEmployee ON CheckEmployee.Employeeid = Historymainline2.Employeeid" & vbCrLf & _
                "WHERE Historymainline.reversalstatus = 1 AND Workingreporthistory.Reporttype2 = 0 AND WorkCenterDispatching.ContainerID = '" & strContainerid & "' " & vbCrLf & _
                "ORDER BY Historymainline.Txndate DESC"

            Dim dtConventionCheck As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            strSQL = "SELECT DISTINCT Specbase.Specname, Employee.Fullname, Employee.Paycard" & vbCrLf & _
                "FROM Checktempinfo" & vbCrLf & _
                "INNER JOIN Container ON Checktempinfo.Containerid = Container.Containerid" & vbCrLf & _
                "INNER JOIN Spec ON Checktempinfo.Specid = Spec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "INNER JOIN Employee ON Employee.Employeeid = Checktempinfo.Submitemployeeid" & vbCrLf & _
                "WHERE Container.ContainerID = '" & strContainerid & "' "

            Dim dtCheckTemp As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            strSQL = "SELECT DISTINCT Specbase.Specname,Spec.Specno, Qualityrecord.Qualityrecordname,  Productnoinfo.Qualityrecordnotes, CheckEmployee.Paycard, " & vbCrLf & _
                "       CheckEmployee.Fullname AS CheckEmployeeName, TO_Char(Qualityrecord.Checkdate,'RRRR-MM-DD') AS Checkdate, Container.Childcount, " & vbCrLf & _
                "       TechnologistEmployee.Fullname AS TechnologistEmployeeName, TO_Char(Qualityrecord.Technologistdate,'RRRR-MM-DD') AS Technologistdate," & vbCrLf & _
                "       Qualityrecord.Checkvalidatenotes, CheckvalidateEmployee.Fullname AS CheckvalidateEmployeeName, TO_Char(Qualityrecord.Checkvalidatedate,'RRRR-MM-DD') AS Checkvalidatedate, CheckvalidateEmployee.Paycard AS CheckvalidatePaycard" & vbCrLf & _
                "FROM Qualityrecord" & vbCrLf & _
                "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "INNER JOIN Qualityrecordproductnoinfo ON Qualityrecord.Qualityrecordid = Qualityrecordproductnoinfo.Qualityrecordid" & vbCrLf & _
                "INNER JOIN Productnoinfo ON Qualityrecordproductnoinfo.Productnoinfoid = Productnoinfo.Productnoinfoid " & vbCrLf & _
                "LEFT OUTER JOIN Employee CheckEmployee ON Qualityrecord.Checkemployeeid = CheckEmployee.Employeeid" & vbCrLf & _
                "LEFT OUTER JOIN Employee TechnologistEmployee ON Qualityrecord.Technologistemployeeid = TechnologistEmployee.Employeeid" & vbCrLf & _
                "LEFT OUTER JOIN Employee CheckvalidateEmployee ON Qualityrecord.Checkvalidateemployeeid = CheckvalidateEmployee.Employeeid" & vbCrLf & _
                "INNER JOIN Container ON Container.Containerid = Qualityrecord.Containerid " & vbCrLf & _
                "WHERE Qualityrecord.ContainerID = '" & strContainerid & "' " & vbCrLf & _
                "ORDER BY Spec.Specno, Specbase.Specname"

            Dim dtQuality As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            dtQuality = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtQuality)

            strSQL = "SELECT Specbase.Specname, Qualityrecord.Qualityrecordname, Productnoinfo.Productno, Productnoinfo.Qualityrecordnotes, " & vbCrLf & _
                 "       QualityDisposeAdvice.Qualitydisposeadvicename, Productnoinfo.Qualitydisposenotes" & vbCrLf & _
                 "FROM Qualityrecord" & vbCrLf & _
                 "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
                 "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                 "INNER JOIN Qualityrecordproductnoinfo ON Qualityrecord.Qualityrecordid = Qualityrecordproductnoinfo.Qualityrecordid" & vbCrLf & _
                 "INNER JOIN Productnoinfo ON Qualityrecordproductnoinfo.Productnoinfoid = Productnoinfo.Productnoinfoid " & vbCrLf & _
                 "LEFT OUTER JOIN QualityDisposeAdvice ON QualityDisposeAdvice.Qualitydisposeadviceid = Productnoinfo.Qualitydisposeadviceid" & vbCrLf & _
                 "WHERE Qualityrecord.ContainerID = '" & strContainerid & "' " & vbCrLf

            Dim dtQualityProductNo As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            strSQL = "SELECT Specbase.Specname, Qualityrecord.Qualityrecordname, FiveBillsInfo.Fivebillno, FiveBillsInfo.Conclusion," & vbCrLf & _
                "       FivebillType.Fivebilltypename" & vbCrLf & _
                "FROM Qualityrecord" & vbCrLf & _
                "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "INNER JOIN QualityRecordFiveBillsInfo ON Qualityrecord.Qualityrecordid = QualityRecordFiveBillsInfo.Qualityrecordid" & vbCrLf & _
                "INNER JOIN FiveBillsInfo ON QualityRecordFiveBillsInfo.Fivebillsinfoid = FiveBillsInfo.Fivebillsinfoid" & vbCrLf & _
                "LEFT OUTER JOIN FivebillType ON FivebillType.Fivebilltypeid = FiveBillsInfo.Fivebilltypeid" & vbCrLf & _
                "WHERE Qualityrecord.ContainerID = '" & strContainerid & "' " & vbCrLf

            Dim dtFiveBill As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            strSQL = "SELECT Specbase.Specname, Employee.Fullname, Container.Containername" & vbCrLf & _
                "FROM SynergicInfo" & vbCrLf & _
                "INNER JOIN Spec ON SynergicInfo.Specid = Spec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                "INNER JOIN Employee ON SynergicInfo.Submitemployeeid = Employee.Employeeid" & vbCrLf & _
                "INNER JOIN Container ON SynergicInfo.Containerid = Container.Containerid" & vbCrLf & _
                "WHERE SynergicInfo.ContainerID = '" & strContainerid & "' " & vbCrLf & _
                "ORDER BY SynergicInfo.Submitdate"
            Dim dtSynergic As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            Dim ds As New DataSet
            ds.Tables.Add(dtMain)
            ds.Tables(0).TableName = "dtMain"
            ds.Tables.Add(dtDataCol)
            ds.Tables(1).TableName = "dtDataCol"
            ds.Tables.Add(dtChild)
            ds.Tables(2).TableName = "dtChild"
            ds.Tables.Add(dtMove)
            ds.Tables(3).TableName = "dtMove"
            ds.Tables.Add(dtMaterialinfo)
            ds.Tables(4).TableName = "dtMaterialinfo"
            ds.Tables.Add(dtSpecLastCheck)
            ds.Tables(5).TableName = "dtSpecLastCheck"
            ds.Tables.Add(dtConventionCheck)
            ds.Tables(6).TableName = "dtConventionCheck"
            ds.Tables.Add(dtCheckTemp)
            ds.Tables(7).TableName = "dtCheckTemp"
            ds.Tables.Add(dtQuality)
            ds.Tables(8).TableName = "dtQuality"
            ds.Tables.Add(dtQualityProductNo)
            ds.Tables(9).TableName = "dtQualityProductNo"
            ds.Tables.Add(dtFiveBill)
            ds.Tables(10).TableName = "dtFiveBill"
            ds.Tables.Add(dtSynergic)
            ds.Tables(11).TableName = "dtSynergic"
            ds.Tables.Add(dtPaihaoGuige)
            ds.Tables(12).TableName = "dtPaihaoGuige"
            oResult.DBDataSet = ds
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    Public Function GetColseMemo(ByVal strContianerName As String) As String Implements uMESContainerInfoPrintIBusiness.GetColseMemo
        Return ""
    End Function

    ''' <summary>
    ''' 根据工艺ID获取工序信息
    ''' add:Wangjh 20180123
    ''' </summary>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoByWorkFlowID(ByVal workflowID As String) As DataTable Implements uMESContainerInfoPrintIBusiness.GetStepInfoByWorkFlowID
        Dim strSql As String = " SELECT NULL containerid,NULL productname,NULL workflowid , wb.workflowname,WT.workflowstepname,sb1.specname,s.specid,s.iskey, WT.sequence,cc.cdoname,WT.SUBWORKFLOWID,WT.SUBWORKFLOWBASEID,s.opdesc,s.specno,S.CONTROLCODE" &
                                " FROM WORKFLOWSTEP WT" &
                               " LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WT.SPECBASEID" &
                               " LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WT.SPECID)" &
                            " LEFT JOIN specbase sb1 ON sb1.specbaseid=s.specbaseid" &
                            " LEFT JOIN workflow w ON w.workflowid=wt.workflowid" &
                            " LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid" &
                            " LEFT JOIN cdodefinition cc ON cc.cdodefid=w.cdotypeid"
        strSql += String.Format(" WHERE WT.WORKFLOWID= '{0}'", workflowID)
        strSql += " ORDER BY WT.SEQUENCE"

        GetStepInfoByWorkFlowID = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取批次所有子工艺的打印信息
    ''' add:Wangjh 20180321
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerPrintInfoForSub(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerPrintInfoForSub
        Return Nothing
    End Function

    ''' <summary>
    ''' 根据母订单获取子订单
    ''' add:Wangjh 20180327
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSonMfgOrder(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMESContainerInfoPrintIBusiness.GetSonMfgOrder
        Dim strSql As String = "SELECT m.mfgordername,m.mfgorderid FROM Userattribute ut" &
                                " LEFT JOIN mfgorder m ON m.mfgorderid=ut.parentid" &
                                " WHERE ut.userattributename='父订单号'"
        strSql += String.Format(" AND ut.attributevalue='{0}'", para("FaMfgOrderName"))
        strSql += String.Format(" AND m.factoryid='{0}'", para("FactoryID"))
        GetSonMfgOrder = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取跟踪卡BOM信息
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerBomPrintInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetContainerBomPrintInfo
        Dim result As New uMESProcessResult
        result.Result = True
        Return result
    End Function


    ''' <summary>
    ''' 查询子工艺指定的工艺员
    ''' add:Wangjh 20180621
    ''' </summary>
    ''' <param name="dic"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSubWorkflowCheckTechnique(ByVal dic As Dictionary(Of String, String)) As DataTable Implements uMESContainerInfoPrintIBusiness.GetSubWorkflowCheckTechnique
        Return New DataTable
    End Function

    ''' <summary>
    ''' 当前序未子工艺查询process_no
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerCurrentSubWorkflow(ByVal containerID As String) As DataTable Implements uMESContainerInfoPrintIBusiness.GetContainerCurrentSubWorkflow
        Return New DataTable
    End Function


    ''' <summary>
    ''' 工艺信息预览
    ''' add:Wangjh 20180829
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkflowPrintView(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerInfoPrintIBusiness.GetWorkflowPrintView
        Return Nothing
    End Function
End Class
